# 创建时间:2022/10/10 18:40
# 创建人:天霄
# 计划 :项目工具
# 文件 :DataBase.py
# 基于 Python 3.8.2
# ========================================
# 连接数据库
# ========================================
import sqlite3
import pymysql
from Common.Common.common import return_dic
from Common import dblog
import atexit


class BuildCursorError(BaseException):
    """建立游标异常"""


class _BaseConnect:
    """公共数据库连接方法，不可直接使用（默认使用MySQL）！需改写"""

    def __init__(self, user=None, password='', db=None, host=None, port=0):
        """初始化数据库参数

        :param user: 用户名
        :param password: 密码
        :param db: 数据库名称
        :param host: 服务器地址
        :param port: 服务器端口
        """
        self.id = id(self)
        self.host = host
        self.user = user
        self.password = password
        self.db = db
        self.port = port
        self.connect = self._myconnect()
        self.cursor = self._mycursor()
        self.layer = '数据库层'
        self._cursor_closed = False
        atexit.register(self.end)

    def end(self):
        """执行sql完毕，自动关闭数据库连接"""
        self.close_cursor()
        self.close_connect()
        atexit.unregister(self.end)

    @property
    def isCursorClosed(self):
        """游标是否关闭标记"""
        return self._cursor_closed

    def _myconnect(self):
        """创建数据库连接"""
        try:
            connect = pymysql.connect(host=self.host, user=self.user, password=self.password, db=self.db,
                                      port=self.port, charset='utf8')
            dblog.info(f'连接数据库成功（ID：{self.id}）', 2)
            return connect
        except Exception as e:
            dblog.exception(f'连接数据库失败（ID：{self.id}），错误：\n{e}\n', 2)
            raise ConnectionError(f'连接数据库失败，错误：\n{e}\n')

    def _mycursor(self):
        """建立游标"""
        try:
            cursor = self.connect.cursor(cursor=pymysql.cursors.DictCursor)
            if cursor:
                dblog.info(f"建立cursor游标成功（ID：{self.id}）", 2)
                self._cursor_closed = False
                return cursor
        except Exception as e:
            self.close_connect()
            dblog.exception(f'建立cursor游标失败（ID：{self.id}），错误：\n{e}\n', 2)
            raise BuildCursorError(f'建立cursor游标失败，错误：\n{e}\n')

    def _return_success(self, data=None):
        """定义成功返回模板"""
        return return_dic(1, self.layer, '成功！', data)

    def _return_fail(self, code, msg):
        """定义失败返回模板"""
        return return_dic(code, self.layer, msg)

    def _return_error(self):
        """定义报错返回模板"""
        return return_dic(-1, self.layer, '程序异常！')

    def select(self, sql: str):
        """查询数据

        :param sql: sql语句
        :return: {code, msg, layer, data:[{数据},{数据}]}
        """
        if not sql.lstrip().upper().startswith('SELECT'):
            dblog.warning(f'输入SQL并非查询语句！请检查是否使用方法错误。输入SQL为：\n{sql}\n', 1)
            return self._return_fail(-2, 'sql语句并非select语句')
        if self.isCursorClosed:
            self.cursor = self._mycursor()
        try:
            self.cursor.execute(sql)
            row = self.cursor.fetchall()
            # tabletopname = self.cursor.description
            dblog.info(f"查询数据成功！你的SQL语句是：\n{sql}\n", 1)
            return self._return_success(row)
        except Exception as e:
            dblog.exception(f'查询数据失败！你的SQL语句是：\n{sql}\n错误：\n{e}\n', 1)
            return self._return_error()

    def insert(self, sql: str, commit=True):
        """插入数据：返回的data是一个ID值"""
        if not sql.lstrip().upper().startswith("INSERT"):
            dblog.warning(f'输入SQL并非插入语句！请检查是否使用方法错误。输入SQL为：\n{sql}\n', 1)
            return self._return_fail(-2, 'sql语句并非insert语句')
        if self.isCursorClosed:
            self.cursor = self._mycursor()
        try:
            self.cursor.execute(sql)
            # new_id = self.connect.insert_id()
            new_id = self.cursor.lastrowid
            if commit:
                self.connect.commit()
            dblog.info(f"插入资料成功！是否提交更改为：{commit}，你的SQL语句是：\n{sql}\n", 1)
            return self._return_success(new_id)
        except Exception as e:
            self.connect.rollback()
            dblog.exception(f'插入资料失败！你的SQL语句是：\n{sql}\n错误：\n{e}\n', 1)
            return self._return_error()

    def update(self, sql: str, commit=True):
        """修改数据"""
        if not sql.lstrip().upper().startswith("UPDATE"):
            dblog.warning(f'输入SQL并非修改语句！请检查是否使用方法错误。输入SQL为：\n{sql}\n', 1)
            return self._return_fail(-2, 'sql语句并非update语句')
        if self.isCursorClosed:
            self.cursor = self._mycursor()
        try:
            self.cursor.execute(sql)
            if commit:
                self.connect.commit()
            dblog.info(f"修改数据成功！是否提交更改为：{commit}，你的SQL语句是：\n{sql}\n", 1)
            return self._return_success()
        except Exception as e:
            self.connect.rollback()
            dblog.exception(f'修改数据失败！你的SQL语句是：\n{sql}\n错误：\n{e}\n', 1)
            return self._return_error()

    def delete(self, sql: str, commit=True):
        """删除数据"""
        if not sql.lstrip().upper().startswith("DELETE"):
            dblog.warning(f'输入SQL并非删除语句！请检查是否使用方法错误。输入SQL为：\n{sql}\n', 1)
            return self._return_fail(-2, 'sql语句并非delete语句')
        if self.isCursorClosed:
            self.cursor = self._mycursor()
        try:
            self.cursor.execute(sql)
            if commit:
                self.connect.commit()
            dblog.info(f"删除数据成功！是否提交更改为：{commit}，你的SQL语句是：\n{sql}\n", 1)
            return self._return_success()
        except Exception as e:
            self.connect.rollback()
            dblog.exception(f'删除数据失败！你的SQL语句是：\n{sql}\n错误：\n{e}\n', 1)
            return self._return_error()

    def commit(self):
        """提交增/删/改操作"""
        try:
            self.connect.commit()
            return self._return_success()
        except Exception as e:
            self.connect.rollback()
            dblog.exception(f'提交失败！错误：{e}', 1)
            return self._return_error()

    def close_cursor(self):
        """关闭游标"""
        try:
            if self.cursor and not self.isCursorClosed:
                self.cursor.close()
                self._cursor_closed = True
                dblog.info(f'关闭游标成功（ID：{self.id}）', 2)
        except Exception as e:
            dblog.exception(f'关闭游标失败（ID：{self.id}），错误：\n{e}\n', 2)
            raise

    def close_connect(self):
        """关闭数据库连裤"""
        try:
            if self.connect:
                self.connect.close()
                self.connect = None
                dblog.info(f'关闭数据库连接成功（ID：{self.id}）', 2)
        except Exception as e:
            dblog.exception(f'关闭数据库连接失败（ID：{self.id}），错误：\n{e}\n', 2)
            raise


class ConnectSQLite(_BaseConnect):
    """连接python自带SQLite轻数据库"""

    def __init__(self, db):
        """初始化数据库参数

        :param db: 数据库文件路径
        """
        super().__init__(db=db)

    def _myconnect(self):
        """创建数据库连接"""
        try:
            connect = sqlite3.connect(self.db)
            dblog.info(f'连接数据库成功（ID：{self.id}）', 2)
            return connect
        except Exception as e:
            dblog.exception(f'连接数据库失败（ID：{self.id}），错误：\n{e}', 2)
            raise ConnectionError(f'连接数据库失败，错误：\n{e}\n')

    def _mycursor(self):
        """建立游标指针"""
        try:
            cursor = self.connect.cursor()
            if cursor:
                dblog.info(f"建立cursor指针成功（ID：{self.id}）", 2)
                self._cursor_closed = False
                return cursor
        except Exception as e:
            self.close_connect()
            dblog.exception(f'建立cursor指针失败（ID：{self.id}），错误：\n{e}', 2)
            raise BuildCursorError(f'建立cursor游标失败，错误：\n{e}\n')






